﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GenerateDB
{
    class SQLQueries
    {
        private Settings _settings = new Settings();

        public string DropDatabase()
        {
            return String.Format("IF EXISTS(SELECT name FROM sys.databases WHERE name = '{0}') DROP DATABASE {1};", _settings.DBName, _settings.DBName);
        }

        public string CreateDB()
        {
            return  String.Format("CREATE DATABASE {0}", _settings.DBName); 
        }
       
        public string UseDB()
        {
            return String.Format("USE {0};", _settings.DBName);
        }

        public const string InsetJournalName = "INSERT INTO Journal (Name, UserID, Encryption) VALUES (@Name, @UserID, @Encryption)";
        public const string InsetrField = "INSERT INTO Field (Name, NameRus, Description, DataCreation, TypeID) VALUES (@Name, @NameRus, @Description, @DataCreation, @TypeID)";
        public const string InsertGroupAdmin = @"INSERT INTO Groups (Name, CanRead, CanAdd, CanExport, CanImport, CanAddField, CanDelField, CanAddUsers, CanDelUsers, CanCreateGroups, CanDelGroups, CanCreateJournals, CanDelJournals)
                                           VALUES (@Name, @CanRead, @CanAdd, @CanExport, @CanImport, @CanAddField, @CanDelField, @CanAddUsers, @CanDelUsers, @CanCreateGroups, @CanDelGroups, @CanCreateJournals, @CanDelJournals)";
        public const string InsertGroupName = "INSERT INTO Groups (Name, CanRead) VALUES (@Name, @CanRead)";
        public const string InsertType = "INSERT INTO Type (Name) VALUES (@Type)";
        public const string InsertUser = "INSERT INTO Users (Name, Password, GroupsID) VALUES (@Name, @Password, @GroupsID)";
        public const string InsetrIdJournalIdColumns = "INSERT INTO Journal_Field (JournalID, FieldID) VALUES (@IdJournal, @IdColumn)";

        public const string SelectType = "SELECT ID FROM Type WHERE Name=@Name";
        public const string SelectIdJournal = "SELECT ID FROM Journal WHERE Name=@Name";
        public const string SelectIdColumn = "SELECT ID FROM Field WHERE Name=@Name";
    }
}
